﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;

namespace sistemaAcademicoFinanceiroEscolas.Relatorios
{
    public partial class relatorioDeInadiplencia : System.Web.UI.Page
    {
        validarValores vV = new validarValores();

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                // Carregar alunos existentes e ativos

                SqlConnection sqlc = new SqlConnection(ConfigurationManager.AppSettings["baseDados"]);
                sqlc.Open();

                SqlConnection sqld = new SqlConnection(ConfigurationManager.AppSettings["baseDados"]);
                sqld.Open();

                string sqlAluno = "select * from aluno order by nomeAluno";

                SqlCommand cmdAluno = new SqlCommand(sqlAluno, sqlc);
                SqlDataReader myReaderAluno = cmdAluno.ExecuteReader();

                ListItem AlunoSelecione = new ListItem();
                AlunoSelecione.Value = "0";
                AlunoSelecione.Text = "Selecione um Aluno";

                lstAluno.Items.Add(AlunoSelecione);

                if (myReaderAluno.HasRows)
                {

                    while (myReaderAluno.Read())
                    {
                        ListItem Aluno = new ListItem();

                        Aluno.Value = myReaderAluno["codigoAluno"].ToString();
                        Aluno.Text = myReaderAluno["nomeAluno"].ToString();

                        lstAluno.Items.Add(Aluno);
                    }

                }
                //Aluno carregados
                myReaderAluno.Close();
                cmdAluno.Dispose();

                // fechando conexão com o banco de dados
                sqlc.Close();

                
            }
        }

        protected void btnListar_Click(object sender, EventArgs e)
        {
            string conteudo = "";

            conteudo += "<table width=\"100%\" border=\"1\" cellpadding=\"5\" cellspacing=\"0\" bordercolor=\"#99CCFF\">";

            conteudo += "<tr>";
            conteudo += "<th>Aluno</th>";
            conteudo += "<th>Telefone residencial</th>";
            conteudo += "<th>Mãe</th>";
            conteudo += "<th>Telefone celular</th>";
            conteudo += "<th>Telefone trabalho</th>";
            conteudo += "<th>Pai</th>";
            conteudo += "<th>Telefone celular</th>";
            conteudo += "<th>Telefone trabalho</th>";
            conteudo += "<th>Valor total de aulas (R$)</th>";
            conteudo += "<th>Valor pago de aulas (R$)</th>";
            conteudo += "<th>Débito / Crédito</th>";
            conteudo += "</tr>";

            SqlConnection sqlc = new SqlConnection(ConfigurationManager.AppSettings["baseDados"]);
            sqlc.Open();

            string select = "select * from aluno ";
            if (lstAluno.SelectedValue != "0")
            {
                select += " where codigoAluno = " + lstAluno.SelectedValue + " order by nomeAluno";
            }

            SqlCommand cmd = new SqlCommand(select, sqlc);
            SqlDataReader dr = cmd.ExecuteReader();

            double valorPago = 0, valorTotal = 0, valorDebitoCredito = 0;
            string telefoneResidencial = "";
            string nomeMae = "";
            string celularMae = "";
            string trabalhoMae = "";
            string nomePai = "";
            string celularPai = "";
            string trabalhoPai = "";

            if (dr.HasRows)
            {
                while (dr.Read())
                {
                    valorTotal = pegarValorTotal(dr["codigoAluno"].ToString());

                    telefoneResidencial = dr["telefoneResidenciaAluno"].ToString();
                    nomeMae = dr["nomeMaeAluno"].ToString();
                    celularMae = dr["celularMaeAluno"].ToString();
                    trabalhoMae = dr["telefoneEmpresaMaeAluno"].ToString();
                    nomePai = dr["nomePaiAluno"].ToString();
                    celularPai = dr["celularPaiAluno"].ToString();
                    trabalhoPai = dr["telefoneEmpresaPaiAluno"].ToString();

                    valorDebitoCredito = valorPago - valorTotal;

                    if (lstStatus.SelectedValue == "1" && valorDebitoCredito < 0)
                    {
                        if (valorDebitoCredito == 0 && valorTotal != 0 && valorPago != 0)
                        {
                            conteudo += "<tr>";

                            conteudo += "<td>" + dr["nomeAluno"].ToString() + "</td>";
                            conteudo += "<td>" + dr["telefoneResidenciaAluno"].ToString() + "</td>";
                            conteudo += "<td>" + dr["nomeMaeAluno"].ToString() + "</td>";
                            conteudo += "<td>" + dr["celularMaeAluno"].ToString() + "</td>";
                            conteudo += "<td>" + dr["telefoneEmpresaMaeAluno"].ToString() + "</td>";
                            conteudo += "<td>" + dr["nomePaiAluno"].ToString() + "</td>";
                            conteudo += "<td>" + dr["celularPaiAluno"].ToString() + "</td>";
                            conteudo += "<td>" + dr["telefoneEmpresaPaiAluno"].ToString() + "</td>";

                            conteudo += "<td>R$ " + vV.validar(valorTotal.ToString()) + "</td>";
                            conteudo += "<td>R$ " + vV.validar(valorPago.ToString()) + "</td>";

                            if (valorDebitoCredito < 0)
                            {
                                conteudo += "<td style=\"color:red\">R$ -" + vV.validar(valorDebitoCredito.ToString().Replace("-", "")) + "</td>";
                            }
                            else
                            {
                                conteudo += "<td style=\"color:blue\">R$ " + vV.validar(valorDebitoCredito.ToString()) + "</td>";
                            }
                            conteudo += "</tr>";
                        }
                        else if (valorDebitoCredito != 0)
                        {
                            conteudo += "<tr>";
                            conteudo += "<td>" + dr["nomeAluno"].ToString() + "</td>";
                            conteudo += "<td>" + dr["telefoneResidenciaAluno"].ToString() + "</td>";
                            conteudo += "<td>" + dr["nomeMaeAluno"].ToString() + "</td>";
                            conteudo += "<td>" + dr["celularMaeAluno"].ToString() + "</td>";
                            conteudo += "<td>" + dr["telefoneEmpresaMaeAluno"].ToString() + "</td>";
                            conteudo += "<td>" + dr["nomePaiAluno"].ToString() + "</td>";
                            conteudo += "<td>" + dr["celularPaiAluno"].ToString() + "</td>";
                            conteudo += "<td>" + dr["telefoneEmpresaPaiAluno"].ToString() + "</td>";
                            conteudo += "<td>R$ " + vV.validar(valorTotal.ToString()) + "</td>";
                            conteudo += "<td>R$ " + vV.validar(valorPago.ToString()) + "</td>";

                            if (valorDebitoCredito < 0)
                            {
                                conteudo += "<td style=\"color:red\">R$ -" + vV.validar(valorDebitoCredito.ToString().Replace("-", "")) + "</td>";
                            }
                            else
                            {
                                conteudo += "<td style=\"color:blue\">R$ " + vV.validar(valorDebitoCredito.ToString()) + "</td>";
                            }
                            conteudo += "</tr>";
                        }
                    }
                    else if (lstStatus.SelectedValue == "2" && valorDebitoCredito >= 0)
                    {
                        if (valorDebitoCredito == 0 && valorTotal != 0 && valorPago != 0)
                        {
                            conteudo += "<tr>";
                            conteudo += "<td>" + dr["nomeAluno"].ToString() + "</td>";
                            conteudo += "<td>" + dr["telefoneResidenciaAluno"].ToString() + "</td>";
                            conteudo += "<td>" + dr["nomeMaeAluno"].ToString() + "</td>";
                            conteudo += "<td>" + dr["celularMaeAluno"].ToString() + "</td>";
                            conteudo += "<td>" + dr["telefoneEmpresaMaeAluno"].ToString() + "</td>";
                            conteudo += "<td>" + dr["nomePaiAluno"].ToString() + "</td>";
                            conteudo += "<td>" + dr["celularPaiAluno"].ToString() + "</td>";
                            conteudo += "<td>" + dr["telefoneEmpresaPaiAluno"].ToString() + "</td>";
                            conteudo += "<td>R$ " + vV.validar(valorTotal.ToString()) + "</td>";
                            conteudo += "<td>R$ " + vV.validar(valorPago.ToString()) + "</td>";

                            if (valorDebitoCredito < 0)
                            {
                                conteudo += "<td style=\"color:red\">R$ -" + vV.validar(valorDebitoCredito.ToString().Replace("-", "")) + "</td>";
                            }
                            else
                            {
                                conteudo += "<td style=\"color:blue\">R$ " + vV.validar(valorDebitoCredito.ToString()) + "</td>";
                            }
                            conteudo += "</tr>";
                        }
                        else if (valorDebitoCredito != 0)
                        {
                            conteudo += "<tr>";
                            conteudo += "<td>" + dr["nomeAluno"].ToString() + "</td>";
                            conteudo += "<td>" + dr["telefoneResidenciaAluno"].ToString() + "</td>";
                            conteudo += "<td>" + dr["nomeMaeAluno"].ToString() + "</td>";
                            conteudo += "<td>" + dr["celularMaeAluno"].ToString() + "</td>";
                            conteudo += "<td>" + dr["telefoneEmpresaMaeAluno"].ToString() + "</td>";
                            conteudo += "<td>" + dr["nomePaiAluno"].ToString() + "</td>";
                            conteudo += "<td>" + dr["celularPaiAluno"].ToString() + "</td>";
                            conteudo += "<td>" + dr["telefoneEmpresaPaiAluno"].ToString() + "</td>";
                            conteudo += "<td>R$ " + vV.validar(valorTotal.ToString()) + "</td>";
                            conteudo += "<td>R$ " + vV.validar(valorPago.ToString()) + "</td>";

                            if (valorDebitoCredito < 0)
                            {
                                conteudo += "<td style=\"color:red\">R$ -" + vV.validar(valorDebitoCredito.ToString().Replace("-", "")) + "</td>";
                            }
                            else
                            {
                                conteudo += "<td style=\"color:blue\">R$ " + vV.validar(valorDebitoCredito.ToString()) + "</td>";
                            }
                            conteudo += "</tr>";
                        }
                    }
                    else if (lstStatus.SelectedValue == "0")
                    {
                        if (valorDebitoCredito == 0 && valorTotal != 0 && valorPago != 0)
                        {
                            conteudo += "<tr>";
                            conteudo += "<td>" + dr["nomeAluno"].ToString() + "</td>";
                            conteudo += "<td>" + dr["telefoneResidenciaAluno"].ToString() + "</td>";
                            conteudo += "<td>" + dr["nomeMaeAluno"].ToString() + "</td>";
                            conteudo += "<td>" + dr["celularMaeAluno"].ToString() + "</td>";
                            conteudo += "<td>" + dr["telefoneEmpresaMaeAluno"].ToString() + "</td>";
                            conteudo += "<td>" + dr["nomePaiAluno"].ToString() + "</td>";
                            conteudo += "<td>" + dr["celularPaiAluno"].ToString() + "</td>";
                            conteudo += "<td>" + dr["telefoneEmpresaPaiAluno"].ToString() + "</td>";
                            conteudo += "<td>R$ " + vV.validar(valorTotal.ToString()) + "</td>";
                            conteudo += "<td>R$ " + vV.validar(valorPago.ToString()) + "</td>";

                            if (valorDebitoCredito < 0)
                            {
                                conteudo += "<td style=\"color:red\">R$ -" + vV.validar(valorDebitoCredito.ToString().Replace("-", "")) + "</td>";
                            }
                            else
                            {

                                conteudo += "<td style=\"color:blue\">R$ " + vV.validar(valorDebitoCredito.ToString()) + "</td>";
                            }
                            conteudo += "</tr>";
                        }
                        else if (valorDebitoCredito != 0)
                        {
                            conteudo += "<tr>";
                            conteudo += "<td>" + dr["nomeAluno"].ToString() + "</td>";
                            conteudo += "<td>" + dr["telefoneResidenciaAluno"].ToString() + "</td>";
                            conteudo += "<td>" + dr["nomeMaeAluno"].ToString() + "</td>";
                            conteudo += "<td>" + dr["celularMaeAluno"].ToString() + "</td>";
                            conteudo += "<td>" + dr["telefoneEmpresaMaeAluno"].ToString() + "</td>";
                            conteudo += "<td>" + dr["nomePaiAluno"].ToString() + "</td>";
                            conteudo += "<td>" + dr["celularPaiAluno"].ToString() + "</td>";
                            conteudo += "<td>" + dr["telefoneEmpresaPaiAluno"].ToString() + "</td>";
                            conteudo += "<td>R$ " + vV.validar(valorTotal.ToString()) + "</td>";
                            conteudo += "<td>R$ " + vV.validar(valorPago.ToString()) + "</td>";

                            if (valorDebitoCredito < 0)
                            {
                                conteudo += "<td style=\"color:red\">R$ -" + vV.validar(valorDebitoCredito.ToString().Replace("-", "")) + "</td>";
                            }
                            else
                            {

                                conteudo += "<td style=\"color:blue\">R$ " + vV.validar(valorDebitoCredito.ToString()) + "</td>";
                            }
                            conteudo += "</tr>";
                        }
                    }
                }
            }

            conteudo += "</table>";

            Panel1.Controls.Add(new LiteralControl(conteudo));

            dr.Close();

            sqlc.Close();
        }

        double pegarValorTotal(string codigoAluno)
        {
            SqlConnection sqlc = new SqlConnection(ConfigurationManager.AppSettings["baseDados"]);
            sqlc.Open();

            string selectAula = "SELECT a.codigoAula, a.codigoProfessor, a.codigoTipoAula, ";
            selectAula += "(select nomeProfessor from professor where a.codigoProfessor = professor.codigoProfessor) as nomeProfessor, ";

            selectAula += "(select nomeAluno from aluno where a.codigoAluno = aluno.codigoAluno) as nomeAluno, ";

            selectAula += "a.codigoAluno, a.codigoGrupo, a.dataAula, a.horaInicialAula, a.horaFinalAula, a.codigoDisciplina, a.descricaoAssuntoAula, ";
            selectAula += "a.aulaRealizada, a.observacoesAula, a.valorHora, a.pago, a.aulaCancelada, a.valorPago, ";
            selectAula += "a.valorTotalSemDesconto, a.valorTotalComDesconto, a.codigoAnoLetivo, a.falta, a.meiaHora, ";

            selectAula += "(select v.aulaValor from valorAula v ";
            selectAula += "where convert(datetime,a.dataAula,103) BETWEEN convert(datetime, v.dataInicial,103) ";
            selectAula += "AND convert(datetime, v.dataFinal,103) and a.codigoTipoAula = v.codigoTipoAula) as valorAula, ";

            selectAula += "(select v.valorValorEspecialAluno from valorEspecialAluno v ";
            selectAula += "where convert(datetime,a.dataAula,103) BETWEEN convert(datetime, v.dataInicialValorEspecialAluno,103) ";
            selectAula += "AND convert(datetime, v.dataFinalValorEspecialAluno,103) and a.codigoAluno = v.codigoAluno) as valorAulaEspecial ";

            selectAula += "FROM aula a ";
            selectAula += "WHERE a.aulaRealizada = 1 and a.codigoAluno = " + codigoAluno;
            selectAula += " order by convert(datetime,a.dataAula,103) desc, CONVERT(INT, SUBSTRING(a.horaInicialAula,1 , 2))  ";

            SqlCommand cmdPrincipal = new SqlCommand(selectAula, sqlc);
            SqlDataReader drPrincipal = cmdPrincipal.ExecuteReader();

            double valorTotal = 0;

            if (drPrincipal.HasRows)
            {
                while (drPrincipal.Read())
                {
                    if (drPrincipal["valorAulaEspecial"].ToString().Replace(" ", "") != "")
                    {
                        try
                        {
                            valorTotal += Convert.ToDouble(drPrincipal["valorAulaEspecial"].ToString());
                        }
                        catch
                        {
                        }
                    }
                    else
                    {
                        try
                        {
                            valorTotal += Convert.ToDouble(drPrincipal["valorAula"].ToString());
                        }
                        catch
                        {
                        }
                    }
                }
            }

            drPrincipal.Close();

            sqlc.Close();

            return valorTotal + pegarValorTotalGrupo(codigoAluno);
        }

        double pegarValorTotalGrupo(string codigoAluno)
        {
            double valor = 0;

            string select = "select * from alunoGrupo where codigoAluno = " + codigoAluno + "";

            SqlConnection sqlc = new SqlConnection(ConfigurationManager.AppSettings["baseDados"]);
            sqlc.Open();
            SqlConnection sqld = new SqlConnection(ConfigurationManager.AppSettings["baseDados"]);
            sqld.Open();

            SqlCommand cmdAlunoGrupo = new SqlCommand(select, sqlc);
            SqlDataReader myReaderAlunoGrupo = cmdAlunoGrupo.ExecuteReader();

            if (myReaderAlunoGrupo.HasRows)
            {
                while (myReaderAlunoGrupo.Read())
                {
                    string selectAula = "SELECT a.codigoAula, a.codigoProfessor, a.codigoTipoAula, ";
                    selectAula += "(select nomeProfessor from professor where a.codigoProfessor = professor.codigoProfessor) as nomeProfessor, ";

                    selectAula += "(select nomeAluno from aluno where aluno.codigoAluno = " + codigoAluno + ") as nomeAluno, ";

                    selectAula += "a.codigoAluno, a.codigoGrupo, a.dataAula, a.horaInicialAula, a.horaFinalAula, a.codigoDisciplina, a.descricaoAssuntoAula, ";
                    selectAula += "a.aulaRealizada, a.observacoesAula, a.valorHora, a.pago, a.aulaCancelada, a.valorPago, ";
                    selectAula += "a.valorTotalSemDesconto, a.valorTotalComDesconto, a.codigoAnoLetivo, a.falta, a.meiaHora, ";

                    selectAula += "(select v.aulaValor from valorAula v ";
                    selectAula += "where convert(datetime,a.dataAula,103) BETWEEN convert(datetime, v.dataInicial,103) ";
                    selectAula += "AND convert(datetime, v.dataFinal,103) and a.codigoTipoAula = v.codigoTipoAula) as valorAula, ";

                    selectAula += "(select v.valorValorEspecialAluno from valorEspecialAluno v ";
                    selectAula += "where convert(datetime,a.dataAula,103) BETWEEN convert(datetime, v.dataInicialValorEspecialAluno,103) ";
                    selectAula += "AND convert(datetime, v.dataFinalValorEspecialAluno,103) and v.codigoAluno = " + codigoAluno + ") as valorAulaEspecial ";

                    selectAula += "FROM aula a ";
                    selectAula += "WHERE a.codigoGrupo = " + myReaderAlunoGrupo["codigoGrupo"].ToString() + " and a.aulaRealizada = 1 ";
                    selectAula += " order by convert(datetime,a.dataAula,103) desc, CONVERT(INT, SUBSTRING(a.horaInicialAula,1 , 2))  ";

                    SqlCommand cmdPrincipal = new SqlCommand(selectAula, sqld);
                    SqlDataReader drPrincipal = cmdPrincipal.ExecuteReader();

                    if (drPrincipal.HasRows)
                    {
                        while (drPrincipal.Read())
                        {
                            if (drPrincipal["valorAulaEspecial"].ToString().Replace(" ", "") != "")
                            {
                                try
                                {
                                    valor += Convert.ToDouble(drPrincipal["valorAulaEspecial"].ToString());
                                }
                                catch
                                {
                                }
                            }
                            else
                            {
                                try
                                {
                                    valor += Convert.ToDouble(drPrincipal["valorAula"].ToString());
                                }
                                catch
                                {
                                }
                            }
                        }
                    }
                    drPrincipal.Close();
                }
            }

            cmdAlunoGrupo.Dispose();
            myReaderAlunoGrupo.Close();
            sqlc.Close();
            sqld.Close();

            return valor;
        }

        double pegarValorPago(string codigoAluno)
        {
            double valorPago = 0;

            SqlConnection sqlc = new SqlConnection(ConfigurationManager.AppSettings["baseDados"]);
            sqlc.Open();

            string select = "select * from pagamento where codigoAluno = " + codigoAluno;

            SqlCommand cmd = new SqlCommand(select, sqlc);
            SqlDataReader dr = cmd.ExecuteReader();

            if (dr.HasRows)
            {
                while (dr.Read())
                {
                    valorPago += Convert.ToDouble(dr["valor"].ToString());
                }
            }

            dr.Close();

            sqlc.Close();

            return valorPago;
        }
    }
}
